<?php
class DealsDABase extends AbstractDbObject {
    /**
     * Table name
     *
     * @var string
     */
    protected $tableName = 'deals';

    /**
     * Table alias
     *
     * @var string
     */
    protected $tableAlias = 'deals';

    /**
     * Create a page
     *
     * @access public
     *
     * @param DealsEntity $dealsEntity: object
     *
     * @return primary key has just been inserted
     */
    public function create($dealsEntity) {
        try {
            // Get db object
            $dbWriter = $this->getDbWriter();

            // Fetch array
            $data = array (
                    'groupon_id' => $dealsEntity->groupon_id,
                    'deal_name' => $dealsEntity->deal_name,
                    'address_id' =>$dealsEntity->address_id,
                    'fine_print' => $dealsEntity->fine_print,
                    'original_price' => $dealsEntity->original_price,
                    'sale_price' => $dealsEntity->sale_price,
                    'bought_count' => $dealsEntity->bought_count,
                    'started_deal' => $dealsEntity->started_deal,
                    'expired_deal' => $dealsEntity->expired_deal,
                    'tipped_date' => $dealsEntity->tipped_date,
                    'rating_average' => $dealsEntity->rating_average,
                    'rating_count' => $dealsEntity->rating_count,
                    'comment_count' => $dealsEntity->comment_count,
                    'payment_type' => $dealsEntity->payment_type,
                    'min_bought' => $dealsEntity->min_bought,
                    'max_bought' => $dealsEntity->max_bought,
                    'status' => $dealsEntity->status,
                    'duration' => $dealsEntity->duration,
            );

            // Insert into database
            $dbWriter->insert($this->tableName, $data);

            // Return primary key has just created
            return $dbWriter->lastInsertId($this->tableName, 'id');
        }
        catch (Exception $ex) {


            throw $ex;
        }
    }

    /**
     * Update a adminBlog
     *
     * @access public
     *
     * @param DealsEntity $dealsEntity: object
     *
     * @return bool true if update success otherwise return false
     */
    public function update($dealsEntity) {
        try {
            // Get db object
            $dbWriter = $this->getDbWriter();

            //echo $dealsEntity->picture;
            //die();

            // Fetch array
            $data = array (
                    'groupon_id' => $dealsEntity->groupon_id,
                    'deal_name' => $dealsEntity->deal_name,
                    'address_id' =>$dealsEntity->address_id,
                    'fine_print' => $dealsEntity->fine_print,
                    'original_price' => $dealsEntity->original_price,
                    'sale_price' => $dealsEntity->sale_price,
                    'bought_count' => $dealsEntity->bought_count,
                    'started_deal' => $dealsEntity->started_deal,
                    'expired_deal' => $dealsEntity->expired_deal,
                    'tipped_date' => $dealsEntity->tipped_date,
                    'rating_average' => $dealsEntity->rating_average,
                    'rating_count' => $dealsEntity->rating_count,
                    'comment_count' => $dealsEntity->comment_count,
                    'payment_type' => $dealsEntity->payment_type,
                    'min_bought' => $dealsEntity->min_bought,
                    'max_bought' => $dealsEntity->max_bought,
                    'status' => $dealsEntity->status,
                    'duration' => $dealsEntity->duration,
            );

            // Update adminBlog
            $idQoute = $dbWriter->quote($dealsEntity->id);
            $where[] = "id = $idQoute";

            // Execute query
            $numRows = $dbWriter->update($this->tableName, $data, $where);
            if ($numRows > 0) {
                return true;
            }
            else {
                return false;
            }
        }
        catch (Exception $ex) {
            throw $ex;
        }
    }

    /**
     * Delete a adminBlog by Id
     *
     * @access public
     *
     * @param int $id: Id of adminBlog
     *
     * @return bool whether delete action is success or not
     */
    public function del($id) {
        try {
            // Get db object
            $dbWriter = $this->getDbWriter();

            // Update adminBlog
            $idQuote = $dbWriter->quote($id);

            // Execute query
            $rowCount = $dbWriter->delete($this->tableName, "id = $idQuote");

            // Return boolean result
            if ($rowCount > 0) {
                return true;
            }
            else {
                return false;
            }
        }
        catch (Exception $ex) {
            throw $ex;
        }
    }

    /**
     * Get DealsEntity object by Id of the adminBlog
     *
     * @access public
     *
     * @param int $id: Id of adminBlog
     *
     * @return DealsEntity object
     */
    public function getById($id) {
        // Get db object
        $dbWriter = $this->getDbWriter();

        // Add quote
        $idQuote = $dbWriter->quote($id);

        // Query for get adminBlog by Id
        $query = "
			SELECT {$this->tableName}.*
			FROM {$this->tableName}
			WHERE {$this->tableName}.id = $idQuote
                ";
        $result = $dbWriter->fetchRow($query);
        if (is_null($result) || !is_array($result)) {

            // Id of the adminBlog does not exist
            return null;
        }
        $dealsEntity = new DealsEntity($result);

        // Return DealsEntity object
        return $dealsEntity;
    }

    /**
     * Get all rows of adminBlog table with search criterias
     *
     * @access public
     *
     * @param array $arrElement: array of search criterias
     * @param string $orderBy: Field and the way ordered (ASC or DESC)
     * @param int $begin: Begin item
     * @param int $limit: Number rows to query
     * @param string $where: added where string
     *
     * @return array of adminBlogEntity object
     */
    public function getItems($arrElement, $orderBy = null, $begin = null, $limit = null, $where = null, $having = null) {
        // Get db object
        $dbWriter = $this->getDbWriter();

        // Query for get all items of user deposit with search criterias
        $query = "
			SELECT {$this->tableName}.*
			FROM {$this->tableName} ";

        // Add where
        $whereTmp = "";
        $whereClause = $this->buildWhereClause($arrElement, $dbWriter, $this->tableName);
        $concat = !empty($whereClause) && !empty($whereTmp) ? ' AND ' : '';
        if (empty($whereClause) && !empty($whereTmp)) {
            $whereClause = ' WHERE ';
        }
        $query .= " {$whereClause}{$concat}{$whereTmp} ";

        if (!empty($where)) {
            $query .= " AND " . $where;
        }

        if (!empty($having)) {
            $query .= " HAVING " . $having;
        }

        // Add order By
        $query .= $this->buildOrderClause($orderBy);

        // Add begin and Limit
        $query .= $this->buildLimitClause($begin, $limit);

        // Execute query
        $result = $dbWriter->fetchAll($query);

        $buffer = null;
        foreach ($result as $element) {
            $dealsEntity = new DealsEntity($element);

            // Add DealsEntity into result
            $buffer[] = $dealsEntity;
        }
        return $buffer;
    }

	public function getItemsAll($arrElement, $orderBy = null, $begin = null, $limit = null, $where = null, $having = null) {
        // Get db object
        $dbWriter = $this->getDbWriter();

        // Query for get all items of user deposit with search criterias
        $query = "
			SELECT D.*,G.*,GC.*,D.id as deal_id, G.id as groupon_id, GC.id as category_id,
			 GC.name as category_name, D.status as deal_status
			FROM {$this->tableName} as D
			JOIN groupon as G ON G.id = D.groupon_id
			JOIN groupon_category as GC ON GC.id = G.category_id
			";

        // Add where
        $whereTmp = "";
        $whereClause = $this->buildWhereClause($arrElement, $dbWriter, 'D');
        $concat = !empty($whereClause) && !empty($whereTmp) ? ' AND ' : '';
        if (empty($whereClause) && !empty($whereTmp)) {
            $whereClause = ' WHERE ';
        }
        $query .= " {$whereClause}{$concat}{$whereTmp} ";
	
        if (!empty($where)) {
            $query .= $where;
        }

        if (!empty($having)) {
            $query .= " HAVING " . $having;
        }

        // Add order By
        $query .= $this->buildOrderClause($orderBy);

        // Add begin and Limit
        $query .= $this->buildLimitClause($begin, $limit);
        // Execute query
        $result = $dbWriter->fetchAll($query);
//
//        $buffer = null;
//        foreach ($result as $element) {
//            $dealsEntity = new DealsEntity($element);
//
//            // Add DealsEntity into result
//            $buffer[] = $dealsEntity;
//        }
        return $result;
    }
    
    public function getDealsOfGroupon($groupon_id = null){
    	// Get db object
        $dbWriter = $this->getDbWriter();

        // Query for get all items of user deposit with search criterias
        $query = "
			SELECT {$this->tableName}.*
			FROM {$this->tableName} ";
        $query .= "RIGHT JOIN groupon ON (groupon.id = {$this->tableName}.groupon_id) ";
        if(!empty($groupon_id)){
        	$query .= "WHERE {$this->tableName}.groupon_id = {$groupon_id}";
        }
        
        // Execute query
        $result = $dbWriter->fetchAll($query);

        $buffer = null;
        foreach ($result as $element) {
            $dealsEntity = new DealsEntity($element);

            // Add DealsEntity into result
            $buffer[] = $dealsEntity;
        }
        return $buffer;
    }
    
    public function getAllDealsOfCity($city){
    	// Get db object
        $dbWriter = $this->getDbWriter();
        $query = " SELECT *, D.id as deal_id
			FROM {$this->tableName} as D
			JOIN groupon as G ON D.groupon_id = G.id 
			";
        $query .= " WHERE G.city_id = {$city}
        			AND D.status = 1
        ";
         $query .= ' ORDER BY rand()';
        // Execute query
        $result = $dbWriter->fetchAll($query);
        return $result;
    }
    public function getAnotherDeals($dealId, $cityId){
    	// Get db object
        $dbWriter = $this->getDbWriter();
        $query = " SELECT *, D.id as deal_id
			FROM {$this->tableName} as D
			JOIN groupon as G ON D.groupon_id = G.id 
			";
        $query .= " WHERE G.city_id = {$cityId}
        			AND D.id != {$dealId}
        			AND D.status > 0
        			AND DATE_FORMAT(D.started_deal, '%Y-%m-%d %H:%i:%s') <= '".date('Y-m-d') . ' ' . date('H:i:s') ."'
        			AND DATE_FORMAT(D.expired_deal, '%Y-%m-%d %H:%i:%s') >= '".date('Y-m-d') . ' ' . date('H:i:s')."' 
        ";
        // Execute query
        $result = $dbWriter->fetchAll($query);
        return $result;
    }
    /**
     * Get total rows of adminBlog
     *
     * @access public
     *
     * @param array $arrElement: array of search criterias
     *
     * @return int total: total rows of adminBlog
     */
    public function getTotals($arrElement) {
        // Get db object
        $dbWriter = $this->getDbWriter();

        // Query for get total records in adminBlog table
        $query = "
			SELECT COUNT(*) as total
			FROM {$this->tableName} ";
        // Add where
        $query .= $this->buildWhereClause($arrElement, $dbWriter);

        // Execute query
        $result = $dbWriter->fetchRow($query);

        if (!is_null($result)) {
            return (int) $result['total'];
        }

        // Return no record
        return 0;
    }
    
    public function getAllDeals($arrElement, $orderBy = null, $begin = null, $limit = null, $where = null, $having = null){
    	// Get db object
        $dbWriter = $this->getDbWriter();
        $query = " SELECT *, D.id as deal_id
			FROM {$this->tableName} as D
			JOIN groupon as G ON D.groupon_id = G.id			
			";
  
         
         // Add where
        $whereTmp = "";
        $whereClause = $this->buildWhereClause($arrElement, $dbWriter);
        $concat = !empty($whereClause) && !empty($whereTmp) ? ' AND ' : '';
        if (empty($whereClause) && !empty($whereTmp)) {
            $whereClause = ' WHERE ';
        }
        $query .= " {$whereClause}{$concat}{$whereTmp} ";

        if (!empty($where)) {
            $query .= $where;
        }
		
        if (!empty($having)) {
            $query .= " HAVING " . $having;
        }
        // Add order By
        $query .= $this->buildOrderClause($orderBy);
        // Add begin and Limit
        $query .= $this->buildLimitClause($begin, $limit);
        
        // Execute query
        $result = $dbWriter->fetchAll($query);
//	echo $query;
        return $result;
    }
}